"""APIs for Packing Information workflow (Excel import, etc.)."""

from __future__ import annotations

import re
from typing import Any, Dict, List, Optional

import frappe
from frappe import _
from frappe.utils.xlsxutils import read_xlsx_file_from_attached_file
from frappe.utils import getdate


def _normalize_header(value: Any) -> str:
    if value is None:
        return ""
    text = str(value).strip().lower()
    # remove spaces and punctuation for tolerant matching
    return re.sub(r"[^a-z0-9\u4e00-\u9fa5]", "", text)


def _coerce_qty(value: Any) -> float:
    if value is None or value == "":
        return 0.0
    try:
        return float(value)
    except Exception:
        try:
            # sometimes formatted like '20 pcs'
            num = re.search(r"-?\d+(?:\.\d+)?", str(value))
            return float(num.group(0)) if num else 0.0
        except Exception:
            return 0.0


def _coerce_positive_int(value: Any) -> Optional[int]:
    """Coerce Excel cell to positive integer; returns None if not numeric/<=0."""
    if value is None or value == "":
        return None
    try:
        if isinstance(value, (int, float)):
            n = int(value)
            return n if n > 0 else None
        s = str(value).strip()
        # allow forms like '3' or '3.0'
        if re.fullmatch(r"\d+(?:\.0+)?", s):
            n = int(float(s))
            return n if n > 0 else None
        return None
    except Exception:
        return None


def _coerce_date(value: Any) -> Optional[str]:
    """Coerce common Excel date/string to YYYY-MM-DD; return None if not parseable."""
    if value in (None, ""):
        return None
    try:
        d = getdate(value)
        return d.isoformat()
    except Exception:
        try:
            s = str(value).strip()
            m = re.match(r"(\d{4})[\-/](\d{1,2})[\-/](\d{1,2})", s)
            if m:
                y, mo, da = int(m.group(1)), int(m.group(2)), int(m.group(3))
                return f"{y:04d}-{mo:02d}-{da:02d}"
        except Exception:
            pass
        return None


@frappe.whitelist()
def parse_packing_excel(file_url: str) -> Dict[str, Any]:
    """
    Read an uploaded Excel (.xlsx) and return rows for `packing_items`.

    Input columns (case/spacing tolerant; Chinese supported):
    - CTN No / 箱号 (optional → maps to `box_no`, numeric part only)
    - SKU / 商家编码 → `item`
    - Product Name / 名称 → `item_name`
    - QTY / 数量 → `qty`

    Returns: { items: [ {item, item_name, qty, box_no?} ], errors: [] }
    """
    if not file_url:
        frappe.throw(_("file_url is required"))

    try:
        matrix = read_xlsx_file_from_attached_file(file_url=file_url)
    except Exception as e:
        frappe.log_error(f"读取Excel失败: {str(e)}", "Packing Excel Import")
        return {"items": [], "errors": [str(e)]}

    if not matrix or len(matrix) < 2:
        return {"items": [], "errors": [_("Empty or invalid Excel file")]}

    # find header row: first non-empty row that contains 'sku' or 'qty'
    header_row_idx = 0
    for i, row in enumerate(matrix[:5]):
        norm = [_normalize_header(v) for v in row]
        if any("sku" in h or "商家编码" in h for h in norm) and any("qty" in h or "数量" in h for h in norm):
            header_row_idx = i
            break

    headers = matrix[header_row_idx]
    keys = [_normalize_header(h) for h in headers]

    def find(*candidates: str) -> Optional[int]:
        cand_norm = [_normalize_header(c) for c in candidates]
        for idx, k in enumerate(keys):
            if any(c in k for c in cand_norm):
                return idx
        return None

    idx_box = find("ctnno", "ctn", "箱号")
    idx_sku = find("sku", "商家编码", "itemcode")
    idx_name = find("productname", "名称", "品名")
    idx_name_cn = find("中文名称", "namecn", "itemnamecn", "chinesename")
    idx_color = find("color", "颜色")
    idx_size = find("size", "尺码")
    idx_batch = find("batchno", "批次", "batch")
    idx_mfd = find("manufacturedate", "productdate", "生产日期")
    idx_exp = find("expiredate", "有效期", "expirydate", "expirationdate")
    idx_qty = find("qty", "数量")

    if idx_sku is None or idx_qty is None:
        return {"items": [], "errors": [_("Required columns not found: SKU and QTY")]}

    items: List[Dict[str, Any]] = []
    errors: List[str] = []

    for row in matrix[header_row_idx + 1 :]:
        # skip empty lines
        if not any(cell not in (None, "") for cell in row):
            continue

        sku = row[idx_sku] if idx_sku < len(row) else None
        if not sku:
            continue

        name = row[idx_name] if (idx_name is not None and idx_name < len(row)) else None
        name_cn = row[idx_name_cn] if (idx_name_cn is not None and idx_name_cn < len(row)) else None
        color = row[idx_color] if (idx_color is not None and idx_color < len(row)) else None
        size = row[idx_size] if (idx_size is not None and idx_size < len(row)) else None
        batch_no = row[idx_batch] if (idx_batch is not None and idx_batch < len(row)) else None
        manufacture_date = _coerce_date(row[idx_mfd]) if (idx_mfd is not None and idx_mfd < len(row)) else None
        expire_date = _coerce_date(row[idx_exp]) if (idx_exp is not None and idx_exp < len(row)) else None
        qty = _coerce_qty(row[idx_qty] if idx_qty < len(row) else None)
        box_no = _coerce_positive_int(row[idx_box]) if (idx_box is not None and idx_box < len(row)) else None

        row_out: Dict[str, Any] = {
            "item": str(sku).strip(),
            "item_name": (str(name).strip() if name else ""),
            "qty": qty,
        }
        if box_no:
            row_out["box_no"] = box_no
        if name_cn:
            row_out["item_name_cn"] = str(name_cn).strip()
        if color:
            row_out["item_color"] = str(color).strip()
        if size:
            row_out["item_size"] = str(size).strip()
        if batch_no:
            row_out["batch_no"] = str(batch_no).strip()
        if manufacture_date:
            row_out["manufacture_date"] = manufacture_date
        if expire_date:
            row_out["expire_date"] = expire_date

        items.append(row_out)

    return {"items": items, "errors": errors}


